This is my first attempt at redesigning a graph according to the design principles taught in class.
Prof. Kam has given us the following graph to work on:
First we analyse the clarity of the graph and the elements (or lack thereof) that impede clarity.
Below you can see the plan for a new and improved visualisation:
The following is the final visualisation:
The link to Tableau Public is here.
| Step | Change | Screenshot |
|---|---|---|
| 1. | Duplicate Excel File and rename to: “Resident Labour Force Participation Rate by Age and Sex Clean.xls” | |
| 2. | Create 3 extra tabs in the Excel File named “Total,” “Male,” and “Female.” | |
| 3. | Copy and paste and the header row from “mrsd_Res_LFPR_2” into each one of the new sheet with the first cell always being A3 | |
| 4. | Copy and paste the respective table to each of the 3 new sheets. Important: paste values only. Save and close the file. | |
| 5. | Change the “Males” and “Females” values in cell A4 of the “Male” and “Female worksheets into “Total” | |
| 6. | Repeat Steps 2-5 using the data from “mrsd_Res_LFPR_1”. This time call the three new sheets “Total Summary,” “Male Summary,” and “Female Summary” | |
| 7. | Open a new Tableau document and import “Resident Labour Force Participation Rate by Age and Sex Clean.xls” | |
| 8. | Drag “Total,” “Male,” “Female,” “Total Summary,” “Male Summary,” and “Female Summary” in as a data source. The relation between tables should be: Age (Years) / Sex = Age (Years) / Sex. | |
| 9. | Hide column “2007A” (“2007a”) in each table. | |
| 10. | For each of the tables select each year column. This means every column from 1991-2021.Right click on any column while holding the selection and click “Pivot.” | |
| 11. | Rename the “Pivot Field Names” column into “Year (Table Name).” | |
| 12. | Change the Year column data type from “String” to “Date” | |
| 13. | Change the “Pivot Field Values” column data type from “String” to “Number (decimal)” | |
| 14. | Create a calculated column by dividing the values in the “Pivot Field Values” column by 100. Name the new column “LFPR (Table Name)” for each table. Hide the “Pivot Field Values” column. | |
| 15. | In each of the relations between the tables, equate the year columns. | |
| 16. | For each table, rename “Age (Years) / Sex” to “Age Group (Table Name).” | |
| 17. | Go to “Sheet 1” and rename it to “Age Group LFPR Comparison” | |
| 18. | In each of the tables on the left hand pane select the respective LFPR column and select “Default Properties” “Number Format.” | |
| 19. | Change the number format to “Percentage” and click “Ok.” Keep 2 decimal places. | |
| 20. | Collapse all tables except for “Total” | |
| 21. | Add the Year component of “Year” to the column pane and “LFPR” to the row pane. Add “Age Group” to the Detail and Color panes. | |
| 22. | From the above step we can see that the data for three years is missing. These years are 1995, 2000, and 2005. However, given that we are interested in the labour force participation rate over time rather than in specific years, we remove these null values from the graph. Click the box labelled “45 nulls” at the bottom right of the page. | |
| 23. | Next, click filter data. This will exclude the three years without data from the graph. | |
| 24. | Given that we have excluded certain values, we have to put in a warning such that any viewer of the graph will now this. Click on the little arrow at the top right of the title box and click “Edit Title” | |
| 25. | Press enter and put in a place holder for the subtitle. Press enter two more times and enter the warning message as shown in the picture. The font should be 8, red, and boldened. | |
| 26. | Exclude the categories “70 & Over” and “Total” from the graph. | |
| 27. | Click the small arrow in the Age Group Legend pane. Click “Edit Colors.” | |
| 28. | Click on the drop-down menu under “Select Color Palette” and choose “Hue Circle.” | |
| 29. | “Assign Palette” and click “Ok.” | |
| 30. | Hover over the y axis , right click, and select “Edit Axis.” | |
| 31. | Go to the “Tick Marks” tab and for Major Tick Marks set “Fixed.” Also set Tick Origin = 0 and Tick Interval = 0.2. | |
| 32. | To make the graph static, we need to turn off the tooltip. Go to Worksheet –> Tooltip | |
| 33. | Uncheck “Show tooltips” and click “Ok.” | |
| 34. | Hover over the blue Year ribbon in the column pane. Click on the little arrow that appears on the right-hand side. Select continuous. | |
| 35. | We can see that the ticks on the x axis automatically change. However, there is a problem, the three missing years are included as ticks. Therefore, a change in tick values is needed. Right click the x axis and select “Edit Axis.” | |
| 36. | Go to the “Tick Marks” tab and for Major Tick Marks set “Fixed.” Also set Tick Origin = 1 and Tick Interval = 5. Select “No” for Minor Tick Marks. | |
| 37. | Go back to “General” and change the axis title to “Year” | |
| 38. | Repeat the previous step for the y axis, renaming it “Labour Force Participation Rate.” | |
| 39. | Right click the x axis and select “Add Reference Line.” | |
| 40. | Add a reference line with following settings: Scope: • Per Pane Line: • Value: 2020 - Constant • Label: Custom - Covid 19 Pandemic • Tooltip: None Formatting: • Line: - - (middle selection) |
|
| 41. | Add a reference line with following settings: Scope: • Per Pane Line: • Value: 1999 - Constant • Label: Custom - SG Retirement increased to 62 • Tooltip: None Formatting: • Line: - - (middle selection) Information Source for this can be found here. |
|
| 42. | Add a third reference line with following settings: Scope: • Per Pane Line: • Value: 2008 - Constant • Label: Custom - Subprime Mortgage Crisis • Tooltip: None Formatting: • Line: - - (middle selection) |
|
| 43. | Add a third reference line with following settings: Scope: • Per Pane Line: • Value: 1997 - Constant • Label: Custom - Asian Financial Crisis • Tooltip: None Formatting: • Line: - - (middle selection) |
|
| 44. | Right click the Asian Financial Crisis reference line and select “Format…” | |
| 45. | Change the alignment to left and up. Repeat this for the other three reference lines with the following allignments: SG Retirement: right and up Subprime Mortgage Crisis: left and down Covid 19 Pandemic: left and down |
|
| 46. | Last, change the title to following text: (font 15) Singapore’s Labour Participation is increasing in almost all age groups despite global crises. (font 10) Labour Force Participation Rate in Singapore across age groups between 1991 and 2021 |
Finally, what can we learn from the graph?
Singapore’s LFPR is increasing for most age groups. Other than the two youngest age groups, not a single one has had a long lasting downwards trend in the past 30 years. This is a positive development for Singapore’s workforce as a trend like this usually entails lower unemployment and poverty within the population.
The age groups in the middle have the highest LFPR. Since 2008 the age groups between 25 and 59 have consistently had higher LFPR than the groups younger and older than them. Specifically, this gap has grown to more than 10% at its narrowest point in 2021.
Singapore’s raise in retirement age has an affect on the “60 to 64” age group. However, there was a delay in policy effect. The implementation happened in 1999, while the first steep rise in this age group only happened around 2003. These policies usually are expected to have a delay.
People age 20 to 24 have a significantly lower LFPR than they had 30 years ago. This might have to do with an increase in Singapore’s wealth which gave more young people the opportunity to attend tertiary education instead of having to work immediately.
The gap between the top middle aged (25-59) age group and the bottom one has closed to a large extent. While in 1991 this gap seems to have been around 40%, it is only approximately 15% in 2021.
Distill is a publication format for scientific and technical writing, native to the web.
Learn more about using Distill for R Markdown at https://rstudio.github.io/distill.